﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Phoenix.AppCode.Entities;
using System.Data.SqlClient;
using System.Data;

namespace Phoenix.AppCode.Data
{
    public class UsersDAO :BaseDAO
    {
        #region Backend method
        /// <summary>
        /// Insert Article Category
        /// </summary>
        /// <param name="users"></param>
        public void Create(Users users)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersCreate", connection);
                command.CommandType = CommandType.StoredProcedure;

                string hashPassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(users.UserName, "SHA1");

                command.Parameters.Add(new SqlParameter("@ID_FirstUsers", users.ID_Users));
                command.Parameters.Add(new SqlParameter("@UserName", users.UserName));
                command.Parameters.Add(new SqlParameter("@Pass", hashPassword));
                command.Parameters.Add(new SqlParameter("@FullName", users.FullName));
                command.Parameters.Add(new SqlParameter("@Position", users.Position));
                command.Parameters.Add(new SqlParameter("@Email", users.Email));
                command.Parameters.Add(new SqlParameter("@Phone", users.Phone));
                command.Parameters.Add(new SqlParameter("@Visible", users.Visible));

                connection.Open();
                command.ExecuteNonQuery();
                //if (paramId.Value != DBNull.Value)
                //Users.ID = (long)paramId.Value;
                //else
                //  throw new DataAccessException(String.Format(Resources.Strings.CreateEntityException, "Category"));
            }
        }

        /// <summary>
        /// Edit Article Category
        /// </summary>
        /// <param name="category"></param>
        public void Edit(Users users)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersEdit", connection);
                command.CommandType = CommandType.StoredProcedure;

                string hashPassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(users.Pass, "SHA1");

                command.Parameters.Add(new SqlParameter("@ID_Users", users.ID_Users));
                command.Parameters.Add(new SqlParameter("@FullName", users.FullName));
                command.Parameters.Add(new SqlParameter("@Position", users.Position));
                command.Parameters.Add(new SqlParameter("@Email", users.Email));
                command.Parameters.Add(new SqlParameter("@Phone", users.Phone));
                command.Parameters.Add(new SqlParameter("@Visible", users.Visible));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Change Profile User
        /// </summary>
        /// <param name="users"></param>
        public void EditProfile(Users users)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersEditProfile", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", users.ID_Users));
                command.Parameters.Add(new SqlParameter("@UserName", users.UserName));
                command.Parameters.Add(new SqlParameter("@FullName", users.FullName));
                command.Parameters.Add(new SqlParameter("@Position", users.Position));
                command.Parameters.Add(new SqlParameter("@Email", users.Email));
                command.Parameters.Add(new SqlParameter("@Phone", users.Phone));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Change Password
        /// </summary>
        /// <param name="users"></param>
        public void UsersChangePassword(Users users)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersChangePassword", connection);
                command.CommandType = CommandType.StoredProcedure;

                string hashPassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(users.Pass, "SHA1");

                command.Parameters.Add(new SqlParameter("@ID_Users", users.ID_Users));
                command.Parameters.Add(new SqlParameter("@Pass", hashPassword));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Update datetime lastlogin
        /// </summary>
        /// <param name="idusers"></param>
        public void UsersLastLogin(String idusers)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUserLastLogin", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Update datetime lastlogout
        /// </summary>
        /// <param name="idusers"></param>
        public void UsersLastLogout(String idusers)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUserLastLogout", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }

        /// <summary>
        /// Add permission
        /// </summary>
        /// <param name="users"></param>
        public void UsersMenuListAddPermission(String idusers, int idmenulist)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersMenuListAddPermission", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));
                command.Parameters.Add(new SqlParameter("@ID_MenuList", idmenulist));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.UpdateEntityException, "Category", category.ID));
            }
        }
        
        /// <summary>
        /// Delete Users
        /// </summary>
        /// <param name="category"></param>
        public void Delete(Users users)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersDelete", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", users.ID_Users));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw new DataAccessException(String.Format(Resources.Strings.DeleteEntityException, "Category", category.ID));
            }
        }

        public DataTable UsersLogin(string usersname, string pass)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersLogin", connection);
                command.CommandType = CommandType.StoredProcedure;

                string hashPassword = System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(pass, "SHA1");

                command.Parameters.Add(new SqlParameter("@UserName", usersname));
                command.Parameters.Add(new SqlParameter("@Pass", hashPassword));

                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show Users All
        /// </summary>
        /// <returns></returns>
        public DataTable UsersList(string idusers)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersList", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));

                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show all menu control of user
        /// </summary>
        /// <param name="idusers"></param>
        /// <param name="language"></param>
        /// <returns></returns>
        public DataTable UserMenuListControl(String idusers, String language)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUserMenuListControl", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));
                command.Parameters.Add(new SqlParameter("@Language", language));

                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show all menulist permission backend
        /// </summary>
        /// <param name="idusers"></param>
        /// <param name="language"></param>
        /// <returns></returns>
        public DataTable UsersMenuListPermission(String idusers, String language)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersMenuListPermission", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));
                command.Parameters.Add(new SqlParameter("@Language", language));

                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Show data by ID
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        public DataTable UsersList_GetByID(int idusers)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add("@ID_Users", SqlDbType.Int).Value = idusers;
                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }

        /// <summary>
        /// Get ID to Edit or Delete
        /// </summary>
        /// <param name="idusers"></param>
        /// <returns></returns>
        public Users GetUsersByID(int idusers)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersEdit_GetByID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));

                connection.Open();
                Users Users = null;
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (reader.Read())
                        Users = CreateUsersFromReader(reader);
                    //else
                    //   throw new DataAccessException(String.Format(Resources.Strings.GetEntityException, "Category", ID));
                }

                return Users;
            }
        }

        private Users CreateUsersFromReader(IDataReader reader)
        {
            Users users = new Users();
            users.ID_Users = (string)reader["ID_Users"];
            users.UserName = (string)reader["UserName"];
            users.Pass = (string)reader["Pass"];
            users.FullName = (string)reader["FullName"];
            users.Position = (string)reader["Position"];
            users.Email = (string)reader["Email"];
            users.Phone = (string)reader["Phone"];
            users.CreateDate = (DateTime)reader["CreateDate"];
            users.LastLogin = (DateTime)reader["LastLogin"];
            users.LastLogout = (DateTime)reader["LastLogout"];
            users.EditDate = (DateTime)reader["EditDate"];
            users.Visible = (Boolean)reader["Visible"];
            return users;
        }

        public DataTable UsersMenuListCheck(String idusers, String url)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("spUsersMenuListCheck", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@ID_Users", idusers));
                command.Parameters.Add(new SqlParameter("@URL", url));

                SqlDataAdapter da = new SqlDataAdapter(command);

                connection.Open();
                DataTable dt = new DataTable();
                da.Fill(dt);

                return dt;
            }
        }
        #endregion
    }
}